package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class LabTestRecordDAO {
    private static final String CREATE_LAB_TEST_RECORD = "INSERT INTO qdlc_lab_test_record (date, status, staffId, patientId, totalPrice) VALUES (?,?,?,?,?);";
    private static final String UPDATE_LAB_TEST_RECORD = "UPDATE qdlc_lab_test_record SET date = ?, status = ?, staffId = ?, patientId = ?, totalPrice = ? WHERE labTestRecordId = ?";
    private static final String GET_LAB_TEST_RECORD_FROM_ID = "SELECT * FROM qdlc_lab_test_record WHERE labTestRecordId = ?;";
    private static final String GET_ALL_LAB_TEST_RECORD = "SELECT * FROM qdlc_lab_test_record;";
    private static final String GET_LAB_TEST_RECORDS_BY_PATIENT_ID = "SELECT * FROM qdlc_lab_test_record WHERE patientId = ?;";
    
    private SimpleDateFormat formatter = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");
    
    
    public LabTestRecord createLabTestRecord(Date date, int status, int staffId, int patientId, float totalPrice) {
       LabTestRecord record = null;

        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(CREATE_LAB_TEST_RECORD,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            st.setString(1,formatter.format(date));
            st.setInt(2, status);
            st.setInt(3, staffId);
            st.setInt(4, patientId);
            st.setFloat(5, totalPrice);
            
            
            int affectedRows = st.executeUpdate();

            // Don't attempt to get keys if there is no affected row
            if (affectedRows > 0) {
                ResultSet keys = st.getGeneratedKeys();

                // Get key, return newUser
                if (keys.next()) {
                    int recordId= keys.getInt(1);
                    
                    record = new LabTestRecord();
                    record.setDate(date);
                    record.setLabTestRecord(recordId);
                    record.setPatientId(patientId);
                    record.setStaffId(staffId);
                    record.setStatus(status);
                    record.setTotalPrice(totalPrice);
                    
                    
                    if (keys != null) {
                        keys.close();
                    }
                }
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return record;
    }

    public void updateStaff(Date date, int status, int staffId, int patientId, float totalPrice, int labTestRecordId) {
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        try {
            PreparedStatement st = conn.prepareStatement(UPDATE_LAB_TEST_RECORD);
            st.setString(1,formatter.format(date));
            st.setInt(2, status);
            st.setInt(3, staffId);
            st.setInt(4, patientId);
            st.setFloat(5, totalPrice);
            st.setInt(6, labTestRecordId);
            st.executeUpdate();

            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }

    public LabTestRecord getLabTestRecord(int labTestRecordId) {
        LabTestRecord record = null;
    
        // TODO: Add exceptions
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_LAB_TEST_RECORD_FROM_ID);

            st.setInt(1, labTestRecordId);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
               record = new LabTestRecord();
               record.setDate(formatter.parse(rs.getString("date")));
               record.setStaffId(rs.getInt("staffId"));
               record.setPatientId(rs.getInt("patientId"));
               record.setLabTestRecord(rs.getInt("labTestRecordId"));
               record.setStatus(rs.getInt("status"));
               record.setTotalPrice(rs.getFloat("totalPrice"));
               
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return record;
    }
    
    public ArrayList<LabTestRecord> getAllConsultationRecord() {
        ArrayList<LabTestRecord> recordList = new ArrayList<LabTestRecord>();
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_ALL_LAB_TEST_RECORD);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
            	LabTestRecord record = new LabTestRecord();
                record.setDate(formatter.parse(rs.getString("date")));
                record.setStaffId(rs.getInt("staffId"));
                record.setPatientId(rs.getInt("patientId"));
                record.setLabTestRecord(rs.getInt("labTestRecordId"));
                record.setStatus(rs.getInt("status"));
                record.setTotalPrice(rs.getFloat("totalPrice"));
                
                
                recordList.add(record);
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return recordList;
    }
    
    public ArrayList<LabTestRecord> getAllLabTestRecordByPatient(Integer patientId) {
        ArrayList<LabTestRecord> recordList = new ArrayList<LabTestRecord>();
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_LAB_TEST_RECORDS_BY_PATIENT_ID);
            st.setInt(1, patientId);
            
            ResultSet rs = st.executeQuery();

            // Found result!
            while (rs.next()) {
            	LabTestRecord record = new LabTestRecord();
                record.setDate(formatter.parse(rs.getString("date")));
                record.setStaffId(rs.getInt("staffId"));
                record.setPatientId(rs.getInt("patientId"));
                record.setLabTestRecord(rs.getInt("labTestRecordId"));
                record.setStatus(rs.getInt("status"));
                record.setTotalPrice(rs.getFloat("totalPrice"));
                
                
                recordList.add(record);
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return recordList;
    }
}
